﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace CIT.WMS.RM.Bll
{
    class Bll_RMOut
    {
        public static DataTable GetOutRow(string billno, int rowNo)
        {
            string sqlcmd = string.Format(@"SELECT top 1 ot.BillNo,ot.BillType,OT.RelatedBill,OT.Storage_SN,OTR.PartNumber,OTR.RowNo,OTR.OrderNo,OTR.PlanQty ,
                                            isnull(OTR.RealQty,0) ReQty,OTR.PlanQty-isnull(OTR.RealQty,0) as RealQty,
                                            OTR.Ext1,OTR.Ext3 FROM [dbo].[wms_T_OutStockRM] OT inner join [dbo].[wms_T_OutStockRMRows] OTR 
                                            on OT.BillNo=OTR.BillNo WHERE (OT.BillType='领料出库' or OT.BillType='非拉动领料出库' 
                                            or OT.BillType='采购退货'or OT.BillType='进口采购退货'or OT.BillType='车载采购退货') 
                                            and  OT.[BillNo]='{0}' and OTR.RowNo='{1}'  and OTR.Status='0' order by OTR.RowNo,OTR.OrderNo ", billno, rowNo);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetRecommendLocationSN(string regionName, string storageSN, string billNo, string productNumber, string version)
        {
            string rowSql = string.Empty;
            if (string.IsNullOrEmpty(storageSN))
            {
                rowSql = string.Format(@"SELECT TOP 10 it.[Storage_SN],it.[Area_SN],it.[Location_SN],it.[PartNumber],it.Qty,sa.Area_Order FROM [dbo].[wms_T_Inventory] it 
LEFT JOIN T_Bllb_StorageArea_tbsa sa ON it.Area_SN=sa.Area_SN
LEFT JOIN T_Bllb_Storage_tbs st ON it.Storage_SN=st.Storage_SN
LEFT JOIN wms_T_StorageRegion sr ON st.Region_SN = sr.Region_SN
WHERE sr.Region_Name='{0}' AND it.PartNumber='{1}' AND it.Status='1' and sa.Assigned_Type='Y' and it.Version='{2}' ORDER BY  it.DateCode,it.EnterTime,sa.Area_Order,sa.Area_SN,it.Location_SN", regionName, productNumber, version);
            }
            else
            {
                rowSql = string.Format(@"SELECT TOP 10 it.[Storage_SN],it.[Area_SN],it.[Location_SN],it.[PartNumber],it.Qty,sa.Area_Order FROM [dbo].[wms_T_Inventory] it 
LEFT JOIN T_Bllb_StorageArea_tbsa sa ON it.Area_SN=sa.Area_SN
LEFT JOIN T_Bllb_Storage_tbs st ON it.Storage_SN=st.Storage_SN
LEFT JOIN wms_T_StorageRegion sr ON st.Region_SN = sr.Region_SN
WHERE sr.Region_Name='{0}' AND it.PartNumber='{1}' AND it.Status='1' and sa.Assigned_Type='Y' and it.Storage_SN='{2}' and it.Version='{3}'  ORDER BY  it.DateCode,it.EnterTime,sa.Area_Order,sa.Area_SN,it.Location_SN", regionName, productNumber, storageSN, version);
            }

            DataTable rowDT = Utils.NMS.GetDataTable(rowSql);

            return rowDT;
        }

        public static DataTable GetSaaInfo(string saaNo)
        {
            string sqlcmd = string.Format(@"select * from dbo.MdcDatVStorage where ReelId='{0}'", saaNo);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable GetOutDetailOverInfo(string billno, string UniqueCode)
        {
            string sqlcmd = string.Format(@"select * from wms_T_OutStockRMRowDetails where UniqueCode='{0}' and BillNo='{1}'", UniqueCode, billno);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }


        public static DataTable GetExpriTime(string partNumber, string Location_SN)
        {
            string sqlcmd = string.Format(@"select * from (
select Ity.Location_SN,Ity.DateCode,Ity.Qty, Mtl.ShelfLifeTime
,
															convert(datetime,SUBSTRING(ISNULL(Ity.DateCode,'19000101'),1,4) + '-' + 
																						   SUBSTRING(ISNULL(Ity.DateCode,'19000101'),5,2) + '-' + 
																							 SUBSTRING(ISNULL(Ity.DateCode,'19000101'),7,2)) as D1
,
dateadd(day,Mtl.ShelfLifeTime,convert(datetime,SUBSTRING(ISNULL(Ity.DateCode,'19000101'),1,4) + '-' + 
																						   SUBSTRING(ISNULL(Ity.DateCode,'19000101'),5,2) + '-' + 
																							 SUBSTRING(ISNULL(Ity.DateCode,'19000101'),7,2))) as D2
from wms_T_Inventory Ity inner join 
MdcdatMaterial Mtl on Ity.PartNumber = Mtl.MaterialCode
where  len(DateCode)=8 and Ity.PartNumber='{0}' and Ity.Location_SN='{1}'  ) c  where DATEDIFF(day,GETDATE(),c.D2)>=0", partNumber, Location_SN);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static bool RMOutStock(string billNo, string boxNo, decimal qty, string locationSN, string partNumber, string dateCode, string lotNo, string CustomerSN, DateTime ExpiryDate, string UniqueCode, string Ext1, string rowno)
        {
            var result = false;

            try
            {

                var inventorySql = string.Format(@"SELECT * FROM [wms_T_Inventory] WHERE Location_SN='{0}' and  PartNumber='{1}'", locationSN, partNumber);

                var inventoryInfo = Utils.NMS.GetDataTable(inventorySql);

                if (inventoryInfo.Rows.Count == 0)
                {
                    return false;
                }

                var inv_Qty = Convert.ToDecimal(inventoryInfo.Rows[0]["Qty"]);

                if (inv_Qty < qty)
                {
                    return false;
                }
                var inventorySqlAll = string.Format(@"SELECT * FROM [wms_T_Inventory] WHERE Location_SN='{0}'", locationSN);

                var inventoryInfoAll = Utils.NMS.GetDataTable(inventorySqlAll);

                int invencount = inventoryInfoAll.Rows.Count;

                string productSearch_Sql = string.Format(@"SELECT sr.* FROM dbo.wms_T_OutStockRMRows sr
LEFT JOIN dbo.wms_T_OutStockRM os ON sr.BillNo=os.BillNo
WHERE  sr.BillNo='{0}' AND sr.PartNumber='{1}' and sr.RowNo='{2}'", billNo, partNumber, rowno);

                DataTable dt = Utils.NMS.GetDataTable(productSearch_Sql);

                if (dt.Rows.Count == 0)
                {
                    return false;
                }
                var row = dt.Rows[0];

                var storageSN = inventoryInfo.Rows[0]["Storage_SN"].ToString();
                var areaSN = inventoryInfo.Rows[0]["Area_SN"].ToString();
                var rowNo = Convert.ToInt32(row["RowNo"]);
                var orderNo = Convert.ToInt32(row["OrderNo"]);
                var relatedBill = row["RelatedBill"].ToString();
                var version = inventoryInfo.Rows[0]["Version"].ToString();
                var unit = inventoryInfo.Rows[0]["Unit"].ToString();

                var guid = inventoryInfo.Rows[0]["Guid"].ToString();

                string sql = string.Format(@"INSERT INTO [dbo].[wms_T_OutStockRMRowDetails]
           ([BillNo]
           ,[RelatedBill]
           ,[PartNumber]
           ,[RowNo]
           ,[OrderNo]
           ,[BoxNo]
           ,[Version]
           ,[DateCode]
           ,[SupLotNo]
           ,[PlanQty]
           ,[RealQty]
           ,[Unit]
           ,[Status]
           ,[Creator]
           ,[CreateTime]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[CustomerSN]
           ,[ExpiryDate]
           ,[UniqueCode],Ext1)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,{3}
           ,{4}
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,'{9}'
           ,'{10}'
           ,'{11}'
           ,'{12}'
           ,'{13}'
           ,getdate()
           ,'{14}'
           ,'{15}'
           ,'{16}'
           ,'{17}'
           ,'{18}'
           ,'{19}','{20}');", billNo, relatedBill, partNumber, rowNo, orderNo, ' ', version, dateCode, lotNo, qty, qty, unit, "0",
               Utils.uContext.UserNamek__BackingField, storageSN, areaSN, locationSN, CustomerSN, ExpiryDate, UniqueCode, Ext1);


                string sql2 = string.Format(@"UPDATE [dbo].[wms_T_OutStockRMRows]
   SET  [RealQty] = (SELECT SUM([RealQty]) FROM [wms_T_OutStockRMRowDetails] WHERE BillNo='{0}' AND RowNo='{1}'  and status<>'7')
 WHERE  BillNo='{0}' AND RowNo='{1}';
UPDATE dbo.wms_T_OutStockRM SET Updator='{2}',UpdateTime=GETDATE() WHERE BillNo='{0}'  ;", billNo, rowNo, Utils.uContext.UserNamek__BackingField);

                string sql3 = string.Empty;
                string sql4 = string.Empty;
                if (inv_Qty == qty)
                {

                    sql3 = string.Format(@"delete  FROM [dbo].[wms_T_Inventory] WHERE    GUID='{0}';", guid);
                    //sql3 = string.Format(@"delete  FROM [dbo].[wms_T_Inventory] WHERE  Location_SN='{0}' and PartNumber='{1}';", locationSN, partNumber);
                    if (invencount < 2)
                    {
                        sql4 = string.Format(@"UPDATE  T_Bllb_StorageLocation_tbsl SET Status_Flag='0' WHERE Location_SN='{0}';", locationSN);
                    }

                    //Utils.NMS.ExcuteSQL(update_loc);

                }
                else if (inv_Qty > qty)
                {
                    sql3 = string.Format(@"UPDATE [wms_T_Inventory] SET Qty=Qty-{0} WHERE GUID='{1}';", qty, guid);
                    //sql3 = string.Format(@"UPDATE [wms_T_Inventory] SET Qty=Qty-{0} WHERE Location_SN='{1}' and PartNumber='{2}';", qty, locationSN, partNumber);

                }


                string sql6 = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO]
           ,[BoxNo]
           ,[LotNo],RelatedBill1,RelatedBill2)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,GETDATE()
           ,'{7}'
           ,'{8}'
           ,'{9}','{10}','{11}');", Guid.NewGuid(), storageSN, areaSN, locationSN, partNumber,
                        "-" + qty, Utils.uContext.UserNamek__BackingField, "O", UniqueCode, lotNo, relatedBill, billNo);

                //Utils.NMS.ExcuteSQL(insertHis_Sql);



                var sqlCmd = (sql + sql2 + sql3 + sql4 + sql6).Trim(';');


                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
 + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }

            }
            catch (Exception ex)
            {

            }
            return result;
        }


        public static DataTable getRMRows(string billno, int rowNo)
        {
            string sqlcmd = string.Format(@"select  * from [dbo].[wms_T_OutStockRMRows] where billno='{0}' and RowNo='{1}'; ", billno, rowNo);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable getRMRowsOver(string billno)
        {
            string sqlcmd = string.Format(@"select  * from [dbo].[wms_T_OutStockRMRows] where billno='{0}' and Status='0'; ", billno);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable getRMDetails(string billno, int rowno)
        {
            string sqlcmd = string.Format(@"select  * from [dbo].[wms_T_OutStockRMRowDetails] where billno='{0}' and RowNo='{1}' and (Status<>'D' and Status<>'7'); ", billno, rowno);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static bool UpdateRMRow(string billNo, string rowno)
        {
            var result = false;
            try
            {
                var sql = string.Format(@"UPDATE [dbo].[wms_T_OutStockRMRows] SET  Status='1',Updator='{0}',
UpdateTime=GETDATE() WHERE BillNo='{1}' and RowNo='{2}';",
Utils.uContext.UserNamek__BackingField, billNo, rowno);

                var sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sql
  + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }


        public static bool UpdateRM(string billNo)
        {
            var result = false;
            try
            {
                var sql = string.Format(@"UPDATE [dbo].[wms_T_OutStockRM] SET  Status='9',Updator='{0}',
UpdateTime=GETDATE() WHERE BillNo='{1}';",
Utils.uContext.UserNamek__BackingField, billNo);

                var sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sql
  + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }

        public static DataTable getInventoryBySN(string Location_SN, string PartNumber)
        {
            string sqlcmd = string.Format(@"select  * from [dbo].[wms_T_Inventory] where Location_SN='{0}' and PartNumber='{1}'; ", Location_SN, PartNumber);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static DataTable getDetailByUniqueCode(string UniqueCode, string billNo)
        {
            string sqlcmd = string.Format(@"select  * from [dbo].[wms_T_OutStockRMRowDetails] where UniqueCode='{0}' and BillNo='{1}'; ", UniqueCode, billNo);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }

        public static bool UpdateRMDetailByUniqueCode(string UniqueCode, string billNo)
        {
            var result = false;
            try
            {
                var sql = string.Format(@"UPDATE [dbo].[wms_T_OutStockRMRowDetails] SET  Status='7',Updator='{0}',
UpdateTime=GETDATE() WHERE UniqueCode='{1}' and BillNo='{2}';",
Utils.uContext.UserNamek__BackingField, UniqueCode, billNo);

                var sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sql
  + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
            }
            return result;
        }


        public static DataTable GetISRMDNum(string billno, string rowno, string order, string DetailNo)
        {
            string sqlcmd = string.Format(@"SELECT * FROM [dbo].[wms_T_InStockRMRowDetails] WHERE [BillNo]='{0}' and [RowNo]='{1}' and [OrderNo]='{2}' and DetailNo='{3}'", billno, rowno, order, DetailNo);
            var result = Utils.NMS.GetDataTable(sqlcmd);
            return result;
        }



        public static bool RMPR(string billNo, string RelatedBill, string partNumber, int rowNo, int orderNo, string version, string dateCode, string lotNo, decimal PlanQty, decimal RealQty, string Unit, string storageSN, string areaSN, string locationSN, string CustomerSN, DateTime ExpiryDate, string UniqueCode, string Ext1, string oldLocation, bool boolToERP, bool InveUsed)
        {
            var result = false;
            var status = "2";
            if (boolToERP)
            {
                status = "0";
            }
            try
            {

                string sql = string.Format(@"INSERT INTO [dbo].[wms_T_OutReturn]
           ([BillNo]
           ,[RelatedBill]
           ,[PartNumber]
           ,[RowNo]
           ,[OrderNo]
           ,[BoxNo]
           ,[Version]
           ,[DateCode]
           ,[SupLotNo]
           ,[PlanQty]
           ,[RealQty]
           ,[Unit]
           ,[Status]
           ,[Creator]
           ,[CreateTime]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[CustomerSN]
           ,[ExpiryDate]
           ,[UniqueCode],Ext1,Ext3)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,{3}
           ,{4}
           ,'{5}'
           ,'{6}'
           ,'{7}'
           ,'{8}'
           ,{9}
           ,{10}
           ,'{11}'
           ,'{12}'
           ,'{13}'
           ,getdate()
           ,'{14}'
           ,'{15}'
           ,'{16}'
           ,'{17}'
           ,'{18}'
           ,'{19}','{20}','{21}');", billNo, RelatedBill, partNumber, rowNo, orderNo, ' ', version, dateCode, lotNo, PlanQty, RealQty, Unit, status,
               Utils.uContext.UserNamek__BackingField, storageSN, areaSN, locationSN, CustomerSN, ExpiryDate, UniqueCode, Ext1, oldLocation);


                //var inventorySql = string.Format(@"DELETE wms_T_OutStockRMRowDetails WHERE Status='7' and  UniqueCode='{0}'", UniqueCode);

                //                string sql2 = string.Format(@"UPDATE [dbo].[wms_T_OutStockRMRows]
                //   SET  [RealQty] = (SELECT SUM([RealQty]) FROM [wms_T_OutStockRMRowDetails] WHERE BillNo='{0}' AND RowNo='{1}' and status<>'7')
                // WHERE  BillNo='{0}' AND RowNo='{1}';", billNo, rowNo, Utils.uContext.UserNamek__BackingField, orderNo);

                string sql3 = string.Empty;
                string sql4 = string.Empty;
                if (!InveUsed)
                {
                    sql3 = string.Format(@"UPDATE [wms_T_Inventory] SET Qty=Qty+{0} WHERE Location_SN='{1}' and PartNumber='{2}';", RealQty, locationSN, partNumber);


                }
                else
                {

                    sql3 = string.Format(@"INSERT INTO [dbo].[wms_T_Inventory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Version]
           ,[DateCode]
           ,[Qty]
           ,[EnterTime]
           ,[Status]
           ,[LotNo]
           ,[Unit]
          )
values( NEWID(),'{0}','{1}','{2}','{3}','{4}','{5}',{6},GETDATE(),'1','{7}','{8}');", storageSN, areaSN, locationSN, partNumber, version, dateCode, RealQty, lotNo, Unit);
                    sql4 = string.Format(@"UPDATE  T_Bllb_StorageLocation_tbsl SET Status_Flag='1' WHERE Location_SN='{0}';", locationSN);
                }


                string sql6 = string.Format(@"INSERT INTO [dbo].[wms_T_InventoryHistory]
           ([Guid]
           ,[Storage_SN]
           ,[Area_SN]
           ,[Location_SN]
           ,[PartNumber]
           ,[Qty]
           ,[Creator]
           ,[CreateTime]
           ,[IO]
           ,[BoxNo]
           ,[LotNo],RelatedBill1,RelatedBill2,Ext3)
     VALUES
           ('{0}'
           ,'{1}'
           ,'{2}'
           ,'{3}'
           ,'{4}'
           ,'{5}'
           ,'{6}'
           ,GETDATE()
           ,'{7}'
           ,'{8}'
           ,'{9}','{10}','{11}','{12}');", Guid.NewGuid(), storageSN, areaSN, locationSN, partNumber,
                        RealQty, Utils.uContext.UserNamek__BackingField, "I", UniqueCode, lotNo, RelatedBill, billNo, "退捡");

                //Utils.NMS.ExcuteSQL(insertHis_Sql);

                //var sqlCmd = (sql + inventorySql + sql2 + sql3 + sql4 + sql6).Trim(';');
                var sqlCmd = (sql + sql3 + sql4 + sql6).Trim(';');

                sqlCmd = @"---开启事务
                begin tran
                begin try  
                " + sqlCmd
 + @"
                end try
                begin catch
                   if(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                      rollback tran  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
                end catch
                if(@@trancount>0)
                commit tran";

                var excRes = Utils.NMS.ExcuteSQL(sqlCmd);

                if (excRes > 0)
                {
                    result = true;
                }

            }
            catch (Exception ex)
            {

            }
            return result;
        }

    }
}
